package com.yf1706.dao.jpa;

import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.yf1706.vo.CountSocialSecurityVO;
import com.yf1706.vo.SocialSecurityVO;
import com.yf1706.bean.SocialSecurity;
/**
 * 
 * @author 锦涛
 *
 */
public interface SocialSecurityJDAO extends JpaRepository<SocialSecurity, Integer>{

	@Query("SELECT new com.yf1706.vo.SocialSecurityVO(s.socialSecurityId,"
			+ "s.emp.empName, s.socialSecurityType.socialSecurityTypeName,"
			+ "s.socialSecurityBaseNumber,s.publicReserveFunds,s.beginDate,"
			+ "s.endDate,s.remark,s.socialSecuritySob.socialSecuritySobId,"
			+ "s.socialSecurityType.socialSecurityTypeId,"
			+ "s.socialSecurityCompanyValue,s.socialSecurityPersonValue) from SocialSecurity s "
			+ "where s.socialSecuritySob.socialSecuritySobId = ?1")
	Page<SocialSecurityVO> findSearch (int id, Pageable pageable);
	
	@Query("SELECT new com.yf1706.vo.SocialSecurityVO(s.socialSecurityId,"
			+ "s.emp.empName, s.socialSecurityType.socialSecurityTypeName,"
			+ "s.socialSecurityBaseNumber,s.publicReserveFunds,s.beginDate,"
			+ "s.endDate,s.remark,s.socialSecuritySob.socialSecuritySobId,"
			+ "s.socialSecurityType.socialSecurityTypeId,"
			+ "s.socialSecurityCompanyValue,s.socialSecurityPersonValue) from SocialSecurity s")
	Page<SocialSecurityVO> findSSAll (Pageable pageable);
	
	@Query("SELECT new com.yf1706.vo.CountSocialSecurityVO(s.emp.empId, "
			+ "s.socialSecurityCompanyValue,s.socialSecurityPersonValue) from"
			+ " SocialSecurity s where s.socialSecuritySob.socialSecuritySobId = ?1 "
			+ "and ((?2 between s.beginDate and s.endDate) or ?2 > s.beginDate)")
	List<CountSocialSecurityVO> findSearchBySidAndDate(int sId,Date countDate);
}
